Amazing SQL!

Postgres Open
2019-09-12

Who am I

• Lætitia Avrot
• PostgreSQL CoC Member
• #PostgresWomen co-founder
• EDB Senior Database Consultant
• mydbanotebook.org
• @l_avrot

The SQL Standard

Different

• Declarative form
• Indentation
• carriage return
• With comments

Turing complete!

Demonstration on PostgreSQL wiki: https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)

Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017

History

• SQL-86
• SQL-89 cancel and replace SQL-86
• SQL-92
• SQL:1999, SQL:2003, SQL:2006
• SQL:2008, SQL:2011, SQL:2013
• SQL:2016

pgexercises.com

(external)

Self Join

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"

Self Join

• Table join with itself
• Available as left, right and internal joins
• Has near always existed

Subquery

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  (
    select
      memref."firstname"
        || ' ' ||
        memref."surname"
    from members as memref
    where
      mem."recommendedby" = memref."memid"
  )
from members as mem

Subquery

(Inside a Select clause)

• Displays a subquery result
• Only for scalar subqueries
• SQL:1999

Subquery

(Inside a Select clause)

select
  ( select
      "columnName"
    from tableName
    where ...
  )
            

CTE

with memref as
(
  select
    "firstname" || ' ' || "surname" as refname
  from
    members
)
select
  mem."firstname" || ' ' || mem."surname",
  memref.refname
from
  members as mem
left outer join memref
on mem."recommendedby" = memref."memid"

Common Table Expressions (CTE)

• "Declared" subquery
• with Clause
• SQL:1999

Common Table Expressions (CTE)

with "mySubqueryName"
  (
    column1,
    column2,
    ...
  )
as
  (
    whatever subquery
  )
select
from "mySubqueryName"
...
            

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"

select
  mem."firstname",
  mem."surname",
  (
    select
      memref."firstname",
      memref."surname",
    from members as memref
    where
      mem."recommendedby" = memref."memid"
)
from members as mem

Lateral join

select
  mem."firstname",
  mem."surname",
    memref."firstname",
  memref."surname"
from members as mem
left join lateral
  (select "firstname",
     "surname"
   from members as mentors
   where
     mentors.memid = mem.recommendedby) as memref
on true

Lateral join

• Each time you think "For each row, I'd like ..."
• Highly addictive
• Since SQL:1999

Or

select
  "firstname",
  "surname"
from members
where
  "firstname" = 'David'
or
  "firstname" = 'Tim'
or
  "firstname" = 'Darren'

In

select
  "firstname",
  "surname"
from members
where
  "firstname" in ('David', 'Tim', 'Darren')

Values

          insert into ("column1",...) values (...);

          values ('Hello world');

Values

select
  "firstname",
  "surname"
from members
inner join
  values
    (('David'),('Tim'),('Darren'))
  as people(firstname)
on members."firstname" = people."firstname"

Values

• To create a "constant" table
• To lower network overhead during Inserts
• To tune some 'in' where clauses
• Since SQL:92

NOT IN

select surname,
  firstname
from members
where "memid" not in
  (
    select memid
    from bookings
  )

NOT EXISTS

select surname,
  firstname
from members
where not exists
  (
    select 1
    from bookings
    where bookings.memid = members.memid
  )

ANTIJOIN

select surname,
  firstname
from members
left outer join "bookings"
on "members"."memid" = "bookings"."memid"
where "bookings".memid is null

Rollup

select facid,
  extract(month from starttime),
  sum(slots) as slots
from cd.bookings
where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime"))
order by "facid",
  extract(month from "starttime")

Rollup

• Hierarchal data
• Different from CUBE
• Since SQL:99

Subquery

select (
  select count(*) from members
) as count,
firstname, surname
        from members
order by joindate

Window functions

select count(*) 
  over(),
  "firstname",
  "surname"
from members
order by joindate 

Window functions

• Data aggregate on non displayed columns
• SQL:2003
• SQL:2011: LEAD, LAG...